package com.pks.datacenter.controller.basic;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.ModelAndView;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.pks.datacenter.aspect.AspectLogTag;
import com.pks.datacenter.config.RedisKeyConfig;
import com.pks.datacenter.dto.DbTableColumnInfo;
import com.pks.datacenter.entity.basic.DbField;
import com.pks.datacenter.entity.basic.DbInfo;
import com.pks.datacenter.entity.basic.DbSource;
import com.pks.datacenter.entity.basic.Tag;
import com.pks.datacenter.handler.RedisHandler;
import com.pks.datacenter.service.basic.DbFieldService;
import com.pks.datacenter.service.basic.DbInfoService;
import com.pks.datacenter.service.basic.DbSourceService;
import com.pks.datacenter.service.basic.JobInfoService;
import com.pks.datacenter.service.basic.TagService;
import com.pks.datacenter.utils.SUtilAes;
import com.pks.datacenter.utils.SUtilDb;
import com.pks.web.context.SystemContext;
import com.pks.web.springmvc.controller.BasicController;
import com.pks.resultful.common.SRemoteResult;
import com.pks.util.UtilString;

@RestController
@RequestMapping("/dbInfo")
@Scope("prototype")
@Component("dbInfoAction")
@Api(tags = "数据表")
public class DbInfoAction extends BasicController {

	private Logger logger = LoggerFactory.getLogger(DbInfoAction.class);
	@Resource
	private DbInfoService dbInfoService;
	@Resource
	private JobInfoService jobInfoService;
	@Resource
	private RedisHandler redisHandler;
	@Resource
	private TagService tagService;
	@Resource
	private DbSourceService dbSourceService;
	@Resource
	private DbFieldService dbFieldService;

	@RequestMapping("/tolist")
	public ModelAndView tolist(Model model) {
		return new ModelAndView("/WEB-INF/ftl/admin/basic/dbinfo_list");
	}

	@RequestMapping("/list")
	public SRemoteResult<List<DbInfo>> list(Long tagId, String name, String nameLike,
			Integer db_type, String db_ip, String db_ipLike, String db_name, String db_nameLike,
			String db_account, String db_accountLike, String table_name, String table_nameLike,
			String http_url, String http_urlLike, Long dbSourceId) {
		Map<String, Object> qbuilder = new HashMap<String, Object>();
		if (tagId != null) {
			qbuilder.put("tagId", tagId);
		}
		if (UtilString.isNotBlank(name)) {
			qbuilder.put("name", name);
		}
		if (UtilString.isNotBlank(nameLike)) {
			qbuilder.put("nameLike", "%" + nameLike.trim() + "%");
		}
		if (db_type != null) {
			qbuilder.put("db_type", db_type);
		}
		if (UtilString.isNotBlank(db_ip)) {
			qbuilder.put("db_ip", db_ip);
		}
		if (UtilString.isNotBlank(db_ipLike)) {
			qbuilder.put("db_ipLike", "%" + db_ipLike.trim() + "%");
		}
		if (UtilString.isNotBlank(db_name)) {
			qbuilder.put("db_name", db_name);
		}
		if (UtilString.isNotBlank(db_nameLike)) {
			qbuilder.put("db_nameLike", "%" + db_nameLike.trim() + "%");
		}
		if (UtilString.isNotBlank(db_account)) {
			qbuilder.put("db_account", db_account);
		}
		if (UtilString.isNotBlank(db_accountLike)) {
			qbuilder.put("db_accountLike", "%" + db_accountLike.trim() + "%");
		}
		if (UtilString.isNotBlank(table_name)) {
			qbuilder.put("table_name", table_name);
		}
		if (UtilString.isNotBlank(table_nameLike)) {
			qbuilder.put("table_nameLike", "%" + table_nameLike.trim() + "%");
		}
		if (UtilString.isNotBlank(http_url)) {
			qbuilder.put("http_url", http_url);
		}
		if (UtilString.isNotBlank(http_urlLike)) {
			qbuilder.put("http_urlLike", "%" + http_urlLike.trim() + "%");
		}
		if (dbSourceId != null) {
			qbuilder.put("dbSourceId", dbSourceId);
		}
		List<DbInfo> list = new ArrayList<DbInfo>();
		int total = 0;
		total = dbInfoService.getTotalRows(qbuilder);
		if (total > 0) {
			list = dbInfoService.findByPage("utime desc", qbuilder);
			List<Long> dbSourceIds = new ArrayList<Long>();
			for (DbInfo dbInfo : list) {
				if (dbInfo.getDbSourceId() != null) {
					dbSourceIds.add(dbInfo.getDbSourceId());
				}
			}
			Map<Long, DbSource> dbSourceMap = new HashMap<Long, DbSource>();
			if (dbSourceIds.size() > 0) {
				dbSourceMap = dbSourceService.findMapByIds(dbSourceIds);
			}
			Map<Long, Tag> tagMap = tagService.findTagMap(Tag.TYPE_DBSOURCE);
			for (DbInfo obj : list) {
				if (obj.getTagId() != null) {
					Tag tag = tagMap.get(obj.getTagId());
					if (tag != null) {
						obj.setShow_tagName(tag.getName());
					}
				}
				if (obj.getDbSourceId() != null && dbSourceMap.containsKey(obj.getDbSourceId())) {
					obj.setShow_dbSourceName(dbSourceMap.get(obj.getDbSourceId()).getName());
				}
				obj.setShow_dbType(DbInfo.DB_TYPE_MAP.get(obj.getDb_type()));
			}
		}
		return SRemoteResult.initPageList(list, total);
	}

	@RequestMapping("/toadd")
	public ModelAndView toadd(Model model) {
		return new ModelAndView("/WEB-INF/ftl/admin/basic/dbinfo_modify");
	}

	@RequestMapping("/tomodify")
	public ModelAndView tomodify(Long id, Model model) {
		DbInfo obj = null;
		if (id != null) {
			obj = dbInfoService.findById(id);
		}
		model.addAttribute("obj", obj);
		return new ModelAndView("/WEB-INF/ftl/admin/basic/dbinfo_modify");
	}

	@AspectLogTag(operType = "删除")
	@RequestMapping("/delete")
	public SRemoteResult<String> delete(HttpServletRequest request, Long id) {
		if (id == null) {
			return SRemoteResult.initError("请选择需要删除的记录！");
		}
		if (jobInfoService.getTotalRows(jobInfoService.initQbuilder("dbused", id)) > 1) {
			return SRemoteResult.initError("该数据源已被使用，无法删除");
		}
		dbInfoService.delete(id);
		redisHandler.del(RedisKeyConfig.KEY_INFO_DBINFO_PREFIX + id);
		return SRemoteResult.initSuccess(null);
	}

	@AspectLogTag(operType = "修改")
	@RequestMapping("/modify")
	public SRemoteResult<String> modify(HttpServletRequest request, DbInfo obj) {
		if (obj == null) {
			return SRemoteResult.initErrorParamMissing(null);
		}
		if (obj.getDb_type() == null || !DbInfo.DB_TYPE_MAP.containsKey(obj.getDb_type())) {
			return SRemoteResult.initError("请选择正确的数据源类型");
		}
		if (DbInfo.db_type_http == obj.getDb_type()) {
			if (StringUtils.isBlank(obj.getHttp_url())) {
				return SRemoteResult.initError("请填写正确的http接口访问地址");
			}
		} else if (DbInfo.db_type_mysql == obj.getDb_type()
				|| DbInfo.db_type_sqlserver == obj.getDb_type()
				|| DbInfo.db_type_oracle == obj.getDb_type()) {

			if (obj.getDbSourceId() == null) {
				return SRemoteResult.initError("请选择数据源");
			}
			DbSource dbSource = dbSourceService.findById(obj.getDbSourceId());
			if (dbSource == null) {
				return SRemoteResult.initError("数据源不存在");
			}
			if (StringUtils.isBlank(obj.getName())) {
				obj.setName(obj.getTable_name() + "@" + dbSource.getName());
			}
			try {
				dbSource.setDb_password(SUtilAes.Decrypt(dbSource.getDb_password()));
				SUtilDb.executeSql(dbSource.getDb_type(), dbSource.getDb_ip(),
						dbSource.getDb_port(), dbSource.getDb_name(), dbSource.getDb_account(),
						dbSource.getDb_password(), "select 1 from `" + obj.getTable_name() + "`");
			} catch (SQLException e) {
				logger.error("", e);
				return SRemoteResult.initError("数据库连接信息填写不正确，无法访问 sqlState:" + e.getSQLState());
			} catch (Exception e) {
				logger.error("", e);
				return SRemoteResult.initError("数据库连接信息填写不正确，无法访问");
			}
		} else {
			return SRemoteResult.initError("暂不支持其他类型。");
		}
		if (StringUtils.isBlank(obj.getOrder_field())) {
			return SRemoteResult.initError("请填写排序自增字段（参数）名");
		}
		if (obj.getField_type() == null
				|| (DbInfo.field_type_number != obj.getField_type() && DbInfo.field_type_time != obj
						.getField_type())) {
			return SRemoteResult.initError("排序自增字段类型错误");
		}
		if (obj.getId() == null) {
			obj.setId(UtilString.getLongUUID());
			dbInfoService.insert(obj);
		} else {
			DbInfo update = dbInfoService.findById(obj.getId());
			if (update == null) {
				return SRemoteResult.initError("数据不存在！");
			}
			if (jobInfoService.getTotalRows(jobInfoService.initQbuilder("dbused", obj.getId())) > 1) {
				return SRemoteResult.initError("该数据源已被使用，无法修改");
			}
			dbInfoService.update(obj);
		}
		redisHandler.del(RedisKeyConfig.KEY_INFO_DBINFO_PREFIX + obj.getId());
		return SRemoteResult.initSuccess("操作成功");
	}

	@RequestMapping("/findInfo")
	public SRemoteResult<DbInfo> findInfo(HttpServletRequest request, Long id) {
		if (id == null) {
			return SRemoteResult.initErrorParamMissing(null);
		}
		DbInfo obj = dbInfoService.findById(id);
		if (obj == null) {
			return SRemoteResult.initErrorDataNone();
		}
		// obj.setDb_password(null);
		return SRemoteResult.initSuccess(obj);
	}
	
	@ApiOperation(value = "数据查询（不分页）")
	@ApiImplicitParams({
			@ApiImplicitParam(name = "dbSourceId", value = "数据库ID", dataType = "Long", paramType = "query") })
	@RequestMapping(value = "/data", method = RequestMethod.POST)
	public SRemoteResult<List<DbInfo>> data(HttpServletRequest request, Long dbSourceId) {
		Map<String, Object> qbuilder = new HashMap<String, Object>();
		if (dbSourceId != null) {
			qbuilder.put("dbSourceId", dbSourceId);
		}
		List<DbInfo> list = new ArrayList<DbInfo>();
		list = dbInfoService.find("utime desc", qbuilder, null);
		return SRemoteResult.initPageList(list, list.size());
	}

	@RequestMapping("/findDbFieldList")
	public SRemoteResult<List<DbField>> findDbFieldList(HttpServletRequest request, Long dbId) {

		DbInfo dbInfo = dbInfoService.findById(dbId);
		if (dbInfo == null) {
			return SRemoteResult.initError("数据不存在");
		}
		if (DbInfo.db_type_mysql != dbInfo.getDb_type()) {
			return SRemoteResult.initError("当前仅支持mysql数据库");
		}
		List<DbField> list = dbFieldService.findByDbId(dbId);
		if (list.size() > 0) {

		} else {
			DbSource dbSource = dbSourceService.findById(dbInfo.getDbSourceId());
			String db_password = SUtilAes.Decrypt(dbSource.getDb_password());
			List<DbTableColumnInfo> columnInfoList = SUtilDb.queryTableColumnList(
					dbSource.getDb_type(), dbSource.getDb_ip(), dbSource.getDb_port(),
					dbSource.getDb_name(), dbSource.getDb_account(), db_password,
					dbInfo.getTable_name());

			for (DbTableColumnInfo obj : columnInfoList) {
				DbField dbField = new DbField();
				dbField.setChName(obj.getColumn_comment());
				dbField.setEnName(obj.getColumn_name());
				list.add(dbField);
			}
		}
		return SRemoteResult.initPageList(list, list.size());
	}

	@RequestMapping("/findDbDataList")
	public SRemoteResult<List<JSONObject>> findDbDataList(HttpServletRequest request, Long dbId,
			String headerParmJson) {
		DbInfo dbInfo = dbInfoService.findById(dbId);
		if (dbInfo == null) {
			return SRemoteResult.initError("数据不存在");
		}
		if (DbInfo.db_type_mysql != dbInfo.getDb_type()) {
			return SRemoteResult.initError("当前仅支持mysql数据库");
		}
		DbSource dbSource = dbSourceService.findById(dbInfo.getDbSourceId());
		String db_password = SUtilAes.Decrypt(dbSource.getDb_password());
		String querySql = "";
		try {
			if (StringUtils.isNotBlank(headerParmJson)) {
				JSONObject json = JSON.parseObject(headerParmJson);
				StringBuilder sb = new StringBuilder();
				for (Map.Entry<String, Object> entry : json.entrySet()) {
					if (StringUtils.isNotBlank(entry.getKey()) && entry.getValue() != null
							&& StringUtils.isNotBlank(String.valueOf(entry.getValue()))) {
						if (StringUtils.isNotBlank(sb)) {
							sb.append(" and ");
						}
						sb.append("`" + entry.getKey() + "` = '" + String.valueOf(entry.getValue())
								+ "'");
					}
				}
				querySql = sb.toString();
			}
		} catch (Exception e) {
			return SRemoteResult.initError("查询参数格式错误");
		}

		String countSql = "select count(*) count from `" + dbInfo.getTable_name() + "`";
		if (StringUtils.isNotBlank(querySql)) {
			countSql += " where " + querySql;
		}
		List<JSONObject> totalList = SUtilDb.queryList(dbSource.getDb_type(), dbSource.getDb_ip(),
				dbSource.getDb_port(), dbSource.getDb_name(), dbSource.getDb_account(),
				db_password, countSql);
		int total = 0;
		if (totalList.size() > 0) {
			total = totalList.get(0).getIntValue("count");
		}
		List<JSONObject> list = new ArrayList<JSONObject>();
		if (total > 0) {
			String sql = "select * from `" + dbInfo.getTable_name() + "`";
			if (StringUtils.isNotBlank(querySql)) {
				sql += " where " + querySql;
			}
			sql += " limit " + SystemContext.getOffset() + "," + SystemContext.getPagesize();
			list = SUtilDb.queryList(dbSource.getDb_type(), dbSource.getDb_ip(),
					dbSource.getDb_port(), dbSource.getDb_name(), dbSource.getDb_account(),
					db_password, sql);
		}
		return SRemoteResult.initPageList(list, total);
	}

}
